import pandas as pd
import pymysql

sql = f"""
        INSERT INTO 
        tax_income_table( 
            addr, 
            id_card, 
            name, 
            calendar_month,
            cumulative_income,
            cumulative_taxincome,
            cumulative_tax,
            entry_date,
            dtime,
            create_time,
            cidsum, 
            version
            ) 
    VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""
conn = None
cursor = None


def _sql_init():
    """
    初始化数据库连接

    -------
    Returns
    -------
    conn:pymysql.connection
        数据库连接对象
    """
    usr = "root"
    passwd = "hdjt2022DATA!"
    host = "192.168.111.205"
    # db = "tax_test"
    db = "tax"
    charset = "utf8"
    try:
        global conn, cursor
        if conn is None:
            conn = pymysql.connect(
                host=host,
                user=usr,
                password=passwd,
                database=db,
                charset=charset
            )
            # 创建游标对象
            cursor = conn.cursor()
    except BaseException:
        if cursor is not None:
            cursor.close()
        if conn is not None:
            conn.close()
        raise ValueError("连接异常！！！！！！！！！！！！！！！！！！！\n请联系数据人员！！！！！！！！！！！！！！！！！！！！")


def colse_commit():
    global conn, cursor
    conn.commit()
    cursor.close()
    conn.close()
    conn = None


def main():
    df = pd.read_excel('1.xlsx')
    df.fillna(0, inplace=True)
    df["addr"] = df["addr"].str.replace("安徽云信", "-")
    print(df)
    _sql_init()
    for i in df.index:
        print(df.loc[i, 'id_card'])
        cursor.execute(sql, (
            df.loc[i, 'addr'],
            df.loc[i, 'id_card'],
            df.loc[i, 'name'],
            df.loc[i, 'calendar_month'],
            df.loc[i, 'cumulative_income'],
            df.loc[i, 'cumulative_taxincome'],
            df.loc[i, 'cumulative_tax'],
            df.loc[i, 'entry_date'],
            df.loc[i, 'dtime'],
            df.loc[i, 'create_time'],
            df.loc[i, 'cidsum'],
            df.loc[i, 'version']
        ))
    conn.commit()
    colse_commit()
    print("完成")


if __name__ == '__main__':
    main()
